# -*- encoding:utf-8 -*-
# 开发团队：大数据组
# 开发者：albert·bing
# 开发时间：2020/7/5 20:13
# 文件名称：yellow_calendar.py
# 开发工具：PyCharm


#  start your code

# import sys
# sys.path.append('/home/hadoop/programs/spider/WTP66_BigdataCrawler')

import pymysql

host='127.0.0.1'
password = '123456'
port = 3306

# 黄历数据入库
def insert_data_dim_trade(data):
    db = pymysql.connect(host=host, user='root', password=password, port=port, db='mytest')
    cursor = db.cursor()
    # sql = "select * from car_param_info limit 10;"
    # sql = 'insert into dim_trade(`tmName`,`detailId`) values(%s,%s)'
    sql = 'insert into dim_trade(`userId`,`statusName`,`appDate`,`tmName`,`regNo`,`applicantCn`,`agent`,`detailId`,\
       `contactPhone`,`contactEmail`,`province`,`city`,`country`,`createTime`,`updateTime`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    cursor.executemany(sql, data)
    cursor.close()
    db.commit()
    db.close()
    print("mysql-插入成功！\n")


def select_trades(num):
    db = pymysql.connect(host=host, user='root', password=password, port=port, db='mytest')
    cursor = db.cursor()
    # sql = "select * from car_param_info limit 10;"
    sql = 'SELECT tmName,detailId from dim_trade GROUP BY tmName,detailId limit '+str(num)+',10000'
    # sql = 'insert into trade(`userId`,`statusName`,`appDate`,`tmName`,`regNo`,`applicantCn`,`agent`,`detailId`,\
    #    `contactPhone`,`province`,`city`,`country`,`createTime`,`updateTime`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    db.commit()
    db.close()
    return result


def insert_detail_info(data):
    db = pymysql.connect(host=host, user='root', password=password, port=port, db='mytest')
    cursor = db.cursor()
    # sql = "select * from car_param_info limit 10;"
    # sql = 'insert into dim_trade(`tmName`,`detailId`) values(%s,%s)'
    sql = 'insert into trade_detail(`detailId`,`trade_name`,`law_status`,`app_num`,`goods_category`,`app_date`,`app_person_name`,`app_person_address`,\
           `preliminary_review_num`,`preliminary_review_date`,`registration_num`,`registration_date`,`exclusive_right_period`) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    cursor.executemany(sql, data)
    cursor.close()
    db.commit()
    db.close()
    print("mysql-插入成功！\n")